package com.rybbaby.tss.platform.sysPreserve.dao.imp;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.*;

import com.rybbaby.tss.core.bean.ForeignKeyBean;
import com.rybbaby.tss.core.bean.TableBean;
import com.rybbaby.tss.core.bean.TableColumnBean;
import com.rybbaby.tss.core.sql.metadata.DatabaseMetadata;
import com.rybbaby.tss.core.sql.metadata.ForeignKeyMetadata;
import com.rybbaby.tss.core.sql.metadata.TableMetadata;
import com.rybbaby.tss.core.utils.PropertiesReadUtil;
import com.rybbaby.tss.core.web.dao.imp.GenericDao;
import com.rybbaby.tss.platform.sysPreserve.dao.IDatabasePreserveDao;
import com.rybbaby.tss.platform.sysPreserve.pojo.ExecSqlResult;
import org.apache.commons.lang.BooleanUtils;
import org.apache.commons.lang.StringEscapeUtils;
import org.apache.commons.lang.StringUtils;
import org.hibernate.dialect.Dialect;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

/**
 * @author 谢进伟
 * @description
 * @createDate 2017/2/28 14:02
 */
@Repository
public class DatabasePreserveDao extends GenericDao<Object> implements IDatabasePreserveDao {

	@Transactional
	public List<TableBean> getAllTables() {
		List<TableBean> allTables = new ArrayList<TableBean>();
		Connection conn = null;
		ResultSet tablesResultSet = null;
		try {
			conn = getConnection();
			DatabaseMetaData connMetaData = conn.getMetaData();
			tablesResultSet = connMetaData.getTables(null , null , null , new String []{"TABLE" , "VIEW"});
			while (tablesResultSet.next()) {
				String cat = tablesResultSet.getString("TABLE_CAT");
				String schema = tablesResultSet.getString("TABLE_SCHEM");
				String name = tablesResultSet.getString("TABLE_NAME");
				String remarks = tablesResultSet.getString("REMARKS");
				String tableType = tablesResultSet.getString("TABLE_TYPE");
				TableBean tb = new TableBean(schema , cat , name , cat , remarks , tableType);
				allTables.add(tb);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			this.closeResouces(conn , null , tablesResultSet);
		}
		return allTables;
	}

	public List<TableColumnBean> getTableColumns(String catalog , String tableName) throws ClassNotFoundException , SQLException , InstantiationException , IllegalAccessException {
		List<TableColumnBean> list = new ArrayList<TableColumnBean>();
		if(StringUtils.isNotBlank(tableName)) {
			Connection conn = null;
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				conn = this.getConnection();
				Class<?> dialectCls = Class.forName(PropertiesReadUtil.getStringProperty("hibernate.dialect"));
				Dialect dialect = (Dialect)dialectCls.newInstance();
				DatabaseMetadata dm = new DatabaseMetadata(conn , dialect);
				String schema = null;
				boolean isQuoted = false;
				Map<String , ForeignKeyBean> foreignKeyMap = new HashMap<String , ForeignKeyBean>();
				List<String> primaryKeysStr = null;
				TableMetadata tableMetadata = dm.getTableMetadata(tableName , schema , catalog , isQuoted);
				if(tableMetadata != null) {
					primaryKeysStr = tableMetadata.getPrimaryKeysStr();
					Map<String , ForeignKeyMetadata> foreignKeys = tableMetadata.getForeignKeys();
					Iterator<String> iterator = foreignKeys.keySet().iterator();
					while (iterator.hasNext()) {
						String key = iterator.next();
						ForeignKeyMetadata foreignKeyMetadata = foreignKeys.get(key);
						String foreignKeyName = foreignKeyMetadata.getName();
						String referenceTableName = foreignKeyMetadata.getReferencedTableName();
						Map<String , String> references = foreignKeyMetadata.getReferences();
						Iterator<String> itera = references.keySet().iterator();
						while (itera.hasNext()) {
							String referenceColumnName = itera.next();
							String foreignKeyColumnName = references.get(referenceColumnName);
							ForeignKeyBean fkb = new ForeignKeyBean(foreignKeyName , referenceTableName , referenceColumnName , foreignKeyColumnName , "");
							foreignKeyMap.put(referenceColumnName , fkb);
						}
					}
				}
				String sql = "SELECT * FROM " + catalog + "." + tableName;
				ps = conn.prepareStatement(sql);
				rs = ps.executeQuery();
				ResultSetMetaData metaData = rs.getMetaData();
				int columnCount = metaData.getColumnCount();
				for(int column = 1 ; column <= columnCount ; column++) {
					String catalogName = metaData.getCatalogName(column);
					String columnClassName = metaData.getColumnClassName(column);
					// String columnLabel = metaData.getColumnLabel(column);
					String columnName = metaData.getColumnName(column);
					String columnTypeName = metaData.getColumnTypeName(column);
					// String schemaName = metaData.getSchemaName(column);
					String dbTableName = metaData.getTableName(column);
					int columnDisplaySize = metaData.getColumnDisplaySize(column);
					int columnType = metaData.getColumnType(column);
					int precision = metaData.getPrecision(column);
					int scale = metaData.getScale(column);
					int nullable = metaData.isNullable(column);
					// boolean currency = metaData.isCurrency(column);
					boolean signed = metaData.isSigned(column);
					boolean autoIncrement = metaData.isAutoIncrement(column);
					boolean caseSensitive = metaData.isCaseSensitive(column);
					boolean readOnly = metaData.isReadOnly(column);
					boolean writable = metaData.isWritable(column);
					boolean searchable = metaData.isSearchable(column);
					TableColumnBean tc = new TableColumnBean();
					tc.setCatalogName(catalogName);
					tc.setColumnClassName(columnClassName);
					tc.setColumnDisplaySize(columnDisplaySize);
					tc.setColumnName(columnName);
					tc.setColumnType(columnType);
					tc.setColumnTypeName(columnTypeName);
					tc.setIsAutoIncrement(autoIncrement);
					tc.setIsCaseSensitive(caseSensitive);
					tc.setIsNullable(BooleanUtils.toBooleanObject(nullable));
					tc.setIsReadOnly(readOnly);
					tc.setIsSearchable(searchable);
					tc.setIsSigned(signed);
					tc.setIsWritable(writable);
					tc.setPrecision(precision);
					tc.setScale(scale);
					tc.setTableName(dbTableName);
					String lowerCaseColumnName = tc.getColumnName().toLowerCase();
					tc.setPrimaryKey(primaryKeysStr != null && primaryKeysStr.contains(lowerCaseColumnName));
					if(foreignKeyMap.containsKey(lowerCaseColumnName)) {
						ForeignKeyBean foreignKeyBean = foreignKeyMap.get(lowerCaseColumnName);
						tc.setForeignKey(true);
						tc.setForeignKeyName(foreignKeyBean.getForeignKeyName());
						tc.setReferenceColumn(foreignKeyBean.getForeignKeyColumnName());
						tc.setReferenceTable(foreignKeyBean.getReferenceTableName());
					}
					tc.setRemarks(tableMetadata.getColumnMetadata(columnName).getRemarks());
					list.add(tc);
				}
			} catch (Exception e) {
				throw e;
			} finally {
				this.closeResouces(conn , ps , rs);
			}
		}
		return list;
	}

	public List<ExecSqlResult> executeSql(String sqls) {
		if(sqls == null) {
			return null;
		}
		List<ExecSqlResult> results = new ArrayList<ExecSqlResult>();
		List<PreparedStatement> statements = new ArrayList<PreparedStatement>();
		List<ResultSet> rss = new ArrayList<ResultSet>();
		StringBuffer newSqls=new StringBuffer();
		String[] split1 = sqls.split("\\n");
		for(String sqlRow : split1) {
			if(!sqlRow.matches("(\\n|\\r|\\s|//)*((#|-)+.*)?(\\n|\\r|\\s|//)*")){//去掉注释行
				newSqls.append("\n");
				newSqls.append(sqlRow);
			}
		}
		try (Connection connection = getConnection()) {
			for(String sql : newSqls.toString().split(";")) {
				if(StringUtils.isBlank(sql)){
					continue;
				}
				String [] split = StringUtils.split(sql , " ");
				if(split != null && split.length > 0) {
					String escapeSql = StringEscapeUtils.escapeSql(sql);
					PreparedStatement statement = connection.prepareStatement(escapeSql);
					String execType = split[0].toUpperCase().trim();
					ExecSqlResult es = new ExecSqlResult(execType);
					es.setSql(escapeSql);
					String upperCaseSql = escapeSql.toUpperCase().replaceAll("\\r|\\n" , " ");
					if(upperCaseSql.matches("^(\\n|\\r|\\s)*(CREATE|INSERT|UPDATE|DELETE|TRUNCATE|DROP|ALTER){1}(\\s|\\n)+.*$")) {// 创建表、新增数据、修改数据、删除数据、清空表、删除表、删除空间、删库、改表结构
						try {
							int row = statement.executeUpdate();
							es.setRow(row);
						} catch (Exception e) {
							es.setErrorMsg(e.getMessage());
						}
						results.add(es);
					} else if(upperCaseSql.matches("^(\\n|\\r|\\s)*(SELECT|DESC|SHOW){1}(\\s|\\n)+.*$")) {// 查询
						try {
							ResultSet rs = statement.executeQuery();
							ResultSetMetaData metaData = rs.getMetaData();
							int columnCount = metaData.getColumnCount();
							for(int i = 1 ; i <= columnCount ; i++) {
								String columnName = metaData.getColumnName(i);
								es.addHead(columnName);
							}
							int rowCount = 0;
							while (rs.next()) {
								Object [] rowData = new Object [columnCount];
								for(int i = 1 ; i <= columnCount ; i++) {
									Object objValue = rs.getObject(i);
									rowData[i - 1] = objValue;
								}
								es.addRowData(rowData);
								rowCount++;
							}
							es.setRow(rowCount);
							rss.add(rs);
						} catch (Exception e) {
							es.setErrorMsg(e.getMessage());
						}
						results.add(es);
					} else {
						// 不支持
					}
					statements.add(statement);
				}
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			for(ResultSet rs : rss) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			for(PreparedStatement statement : statements) {
				try {
					statement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return results;
	}

	private Connection getConnection() throws ClassNotFoundException , SQLException {
		Connection conn;
		String driver = PropertiesReadUtil.getStringProperty("jdbc.driver");
		String url = PropertiesReadUtil.getStringProperty("jdbc.url");
		String password = PropertiesReadUtil.getStringProperty("jdbc.password");
		String username = PropertiesReadUtil.getStringProperty("jdbc.username");
		Class.forName(driver);
		Properties props = new Properties();
		// 设置可以获取remarks信息
		props.setProperty("remarks" , "true");
		props.setProperty("useInformationSchema" , "true");
		// 基本连接参数
		props.put("user" , username);
		props.put("password" , password);
		conn = DriverManager.getConnection(url , props);
		return conn;
	}

	/**
	 * 关闭相关数据操作资源
	 *
	 * @param conn
	 *            连接对象
	 * @param ps
	 *            操作对象
	 * @param rs
	 *            查询结果集
	 */
	private void closeResouces(Connection conn , PreparedStatement ps , ResultSet rs) {
		if(rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(ps != null) {
			try {
				ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}
